﻿using System;
using System.Collections.Generic;
using System.Text;
using XXX.Models;
using fuzhu;
using System.Data;
using XXX.Models.XXXEntities;
using LH.U8API.Bus;
using ADODB;
using XXX.Common;
using System.Data.SqlClient;
using GLY_XieTong;
using Newtonsoft.Json;
using LH.Model.PU;
using XXX.Models.OtherEntities;

namespace XXX.Bo
{
    public static partial class DBService
    {




        public static string cYear;
        public static string cDate;
        public static string cSubId;
        public static string cSerial = "";

        /// <summary>
        /// 获得多账套下的数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static RetModel2 GetConstrByaccount(string account)
        {
            try
            {
                
               string constr = Common.AppSettings.GetAppSeting("DBList:"+ account);
                if (string.IsNullOrEmpty(constr))
                {
                    return ReErrObject("未配置账套数据库");
                }
                else
                {
                    return ReOKObject(constr);
                }
              

            }
            catch (Exception ex)
            {
                return ReErrObject(ex.Message); 
            }

        }


        /// <summary>
        /// 获得OA对应的组织连接字符串
        /// </summary>
        /// <param name="dbid"></param>
        /// <returns></returns>
        public static string GetOAConStr(string dbid)
        {
            string sql = string.Format(@"select CSOURCE from zdy_inter_database where dbid='{0}' ", dbid);
            DataTable dt2 = DbHelper2.ExecuteTable(sql, CanShu.conStrInter);
            if (dt2.Rows.Count > 0)
            {
                string cConstr2 = DbHelper.GetDbString(dt2.Rows[0]["CSOURCE"]);
                return cConstr2;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得IMS对应的组织连接字符串
        /// </summary>
        /// <param name="dbid"></param>
        /// <returns></returns>
        public static string GetConStr(string dbid)
        {
            string sql = string.Format(@"select CSOURCE from zdy_inter_database where DBID='{0}' ", dbid);
            DataTable dt2 = DbHelper2.ExecuteTable(sql, CanShu.conStrInter);
            if (dt2.Rows.Count > 0)
            {
                string cConstr2 = DbHelper.GetDbString(dt2.Rows[0]["CSOURCE"]);
                return cConstr2;
            }
            else
            {
                return null;
            }
        }

        public static string GetUserLogin(string dbid)
        {
            //20220413 更改，只获取日期，减少数据库读取

            //try
            //{
            //    string sql = "SELECT ServerIP,UserName,UserPass,U8Account FROM dbo.zdy_inter_database WHERE DBID='1' ";
            //    DataTable dt = DbHelper2.ExecuteTable(sql, conStr);
            //    if (dt.Rows.Count > 0)
            //    {
            //        cServer = DbHelper.GetDbString(dt.Rows[0]["ServerIP"]);
            //        cUserid = DbHelper.GetDbString(dt.Rows[0]["UserName"]);
            //        cPassword = DbHelper.GetDbString(dt.Rows[0]["UserPass"]);
            //        cZT = DbHelper.GetDbString(dt.Rows[0]["U8Account"]);
            cYear = DateTime.Now.Year.ToString();
            cDate = DateTime.Now.ToString("yyyy-MM-dd");
            //}
            //else
            //{
            //    return "数据源未配置";
            //}



            //}
            //catch (Exception ex)
            //{
            //    return  ex.Message ;
            //}
            return "ok";
        }

        /// <summary>
        /// U8登陆验证
        /// </summary>
        /// <param name="userid">账号</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public static RetModelbool AA_GetUserLoginU8(string conStr, string userid, string password)
        {
            //使用sql的验证方式
            try
            {
                //string conStr = Common.AppSettings.GetAppSeting(corg);
                //if (string.IsNullOrEmpty(conStr))
                //{
                //    throw new Exception("未配置账套数据库");
                //}
                cYear = DateTime.Now.Year.ToString();
                cDate = DateTime.Now.ToString("yyyy-MM-dd");

                String sql = string.Format(@" SELECT cUser_Name,cPassword,UFSYSTEM.dbo.FnBase64Encode( HASHBYTES('SHA1','{1}'))+CHAR(3) as pwdlogin   
                    FROM UFSYSTEM..UA_User  WHERE cUser_Id='{0}' ", userid, password);
                DataTable dt = DbHelper2.ExecuteTable(sql, conStr);
                if (dt.Rows.Count > 0)
                {
                    string cUserName = DbHelper.GetDbString(dt.Rows[0]["cUser_Name"]);
                    string cPwd = DbHelper.GetDbString(dt.Rows[0]["cPassword"]);
                    string cPwdlogin = DbHelper.GetDbString(dt.Rows[0]["pwdlogin"]);
                    if (cPwd == cPwdlogin)
                    {
                        return ReOKbool(cUserName);
                    }
                    else
                    {
                        throw new Exception("密码不正确");
                    }
                }
                else
                {
                    throw new Exception("账号不存在");
                }

            }
            catch (Exception ex)
            {
                return ReErrorbool(ex.Message);
            }

        }



     
        /// <summary>
        /// 获取仓库的权限
        /// </summary>
        /// <param name="cwhcode">仓库编码</param>
        /// <returns></returns>
        public static RetModel<WMS_PDA_AUTH> GetPdaWarehouseAuth(string cwhcode)
        {
            try
            {


                List<WMS_PDA_AUTH> lst = SqlsugarHelper.Instance.Queryable<WMS_PDA_AUTH>().Where(it => it.Whcode == cwhcode).ToList();

                if (lst.Count > 0)
                {
                    return DBService.ReOK<WMS_PDA_AUTH>(lst);
                }
                else
                {
                    return DBService.ReError<WMS_PDA_AUTH>("没有明细");
                }
            }
            catch (Exception ex)
            {
                return DBService.ReError<WMS_PDA_AUTH>(ex.Message);
            }
        }
      
       

    
        /// <summary>
        /// 读取币种汇率
        /// </summary>
        /// <param name="cOrg">年度</param>
        /// <param name="cWhCode">月份</param>
        /// <param name="cPosCode">币种</param>
        /// <returns></returns>
        public static decimal AA_GetExchFlat(string iyear, string imonth, string cexchname)
        {
            try
            {



                string sqlwh = string.Format(@"select nflat from exch where iYear ='{0}'  and  iperiod ='{1}' and cexch_name='{2}' and Itype=2 ", iyear, imonth, cexchname);

                object nflat = DbHelper2.ExecuteScalar(sqlwh, CanShu.conStr);
                if (nflat == null)
                {
                    return 0;
                }
                else
                {
                    return DbHelper.GetDbdecimal(nflat);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }

        }
        /// <summary>
        /// 获得未审核的盘点单
        /// </summary>
        /// <param name="cTable">查询的表明，WMS_TASK,WMS_RDRECORD</param>
        /// <returns></returns>
        public static RetModel ST_GetPanDian(string corg, string ccode)
        {
            try
            {
                string conStr = Common.AppSettings.GetAppSeting(corg);
                if (string.IsNullOrEmpty(conStr))
                {
                    throw new Exception("未配置账套数据库");
                }
                RetModel retModel = new RetModel();
                string sql = string.Format(@"SELECT a.id, cCVCode,dCVDate,A.cWhCode,B.cWhName FROM dbo.CheckVouch A,
dbo.Warehouse B   WHERE  A.cWhCode=B.cWhCode AND  ISNULL(cAccounter,'')=''  AND dCVDate>='{0}' ", CanShu.dBegin
);

                DataTable dt = DbHelper2.ExecuteTable(sql, conStr);

                if (dt.Rows.Count > 0)
                {
                    List<PanDianVouch> u8wms001s1 = TableListConverter<PanDianVouch>.TableToList2(dt);
                    List<object> u8wms002s = u8wms001s1.ConvertAll(s => (object)s);
                    return DBService.ReOK(u8wms002s);
                }
                else
                {
                    return DBService.ReError("没有未审核的盘点单");
                }

            }
            catch (Exception ex)
            {
                return DBService.ReError(ex.Message);
            }
        }
        /// <summary>
        /// 获得未审核的盘点单
        /// </summary>
        /// <param name="cTable">查询的表明，WMS_TASK,WMS_RDRECORD</param>
        /// <returns></returns>
        public static RetModel ST_GetPanDianDetail(string corg, string ccode)
        {
            try
            {
                string conStr = Common.AppSettings.GetAppSeting(corg);
                if (string.IsNullOrEmpty(conStr))
                {
                    throw new Exception("未配置账套数据库");
                }
                RetModel retModel = new RetModel();
                string sql = string.Format(@"SELECT b.id,autoID, cast(a.irowno as varchar(20)) AS irowno, a.cInvCode,i.cInvName,i.cInvStd,a.cCVBatch,a.cposition as cPosCode,
convert(varchar(20),convert(real,iCVQuantity)) as iCVQuantity,convert(varchar(20),convert(real,iCVCQuantity )) as iCVCQuantity,cFree1,cFree2,a.cCVCode,
CONVERT(VARCHAR(20),dMadeDate,23) AS dMadeDate,b.cWhCode,
c.cWhName,cast(0 as decimal(18,6) ) as scanqty
 FROM  CheckVouchs a,dbo.CheckVouch b,warehouse c,inventory i 
  WHERE a.cCVCode=b.cCVCode AND b.cWhCode=c.cWhCode and a.cinvcode=i.cinvcode
and a.cCVCode ='{0}' ", ccode);

                DataTable dt = DbHelper2.ExecuteTable(sql, conStr);

                if (dt.Rows.Count > 0)
                {
                    List<PanDianVouchs> u8wms001s1 = TableListConverter<PanDianVouchs>.TableToList2(dt);
                    List<object> u8wms002s = u8wms001s1.ConvertAll(s => (object)s);
                    return DBService.ReOK(u8wms002s);
                }
                else
                {
                    return DBService.ReError("没有盘点单明细");
                }

            }
            catch (Exception ex)
            {
                return DBService.ReError(ex.Message);
            }
        }

        /// <summary>
        /// 获得未审核的盘点单
        /// </summary>
        /// <param name="cTable">查询的表明，WMS_TASK,WMS_RDRECORD</param>
        /// <returns></returns>
        public static RetModel GetPanDian_barcodes(string ccode)
        {
            try
            {
                RetModel retModel = new RetModel();
                string sql = string.Format(@"SELECT SOURCECODE  AS cCVCode,a.BARCODENO,a.CTYPE,b.INVCODE,b.TOTALQTY FROM dbo.WMS_RDRECORDS_ORI a
INNER JOIN dbo.WMS_BARCODE b ON a.BARCODENO=b.BARCODENO
where a.ctype='盘点' and SOURCECODE ='{0}' ", ccode);

                DataTable dt = DbHelper2.ExecuteTable(sql, CanShu.conStr);

                if (dt.Rows.Count > 0)
                {
                    List<PanDianVouch_barcodes> u8wms001s1 = TableListConverter<PanDianVouch_barcodes>.TableToList2(dt);
                    List<object> u8wms002s = u8wms001s1.ConvertAll(s => (object)s);
                    return DBService.ReOK(u8wms002s);
                }
                else
                {
                    return DBService.ReError("没有盘点单明细");
                }

            }
            catch (Exception ex)
            {
                return DBService.ReError(ex.Message);
            }
        }

        #region 获得现存量
        public static RetModel<ReportInvStock> Rep_GetInvStock(string corg, string cwhcode, string cinvcode)
        {
            try
            {
                //string conStr = Common.AppSettings.GetAppSeting(corg);
                //if (string.IsNullOrEmpty(conStr))
                //{
                //    throw new Exception("未配置账套数据库");
                //}
                string condition = "";
                if (!string.IsNullOrEmpty(cwhcode) && cwhcode != "")
                {
                    condition += String.Format(@" and a.cWhCode='{0}'", cwhcode);
                }
                if (!string.IsNullOrEmpty(cinvcode) && cinvcode != "")
                {
                    condition += String.Format(@" and A.cInvCode='{0}'", cinvcode);
                }
                string sql = String.Format(@"SELECT a.cWhCode,b.cWhName,a.cInvCode,i.cInvName,convert(varchar(20),convert(real,SUM(a.iQuantity))) AS iQuantity 
FROM dbo.CurrentStock a,warehouse b ,dbo.Inventory i
 WHERE a.cWhCode=b.cwhcode AND a.cInvCode=i.cInvCode {0} and a.iQuantity<>0  
 GROUP BY a.cWhCode,b.cWhName,a.cInvCode,i.cInvName", condition);

                List<ReportInvStock> lst = SqlsugarHelper._Instance.Ado.SqlQuery<ReportInvStock>(sql).ToList();
                if (lst.Count > 0)
                {
                    return DBService.ReOK<ReportInvStock>(lst);
                }
                else
                {
                    return DBService.ReError<ReportInvStock>("没有现存量");
                }


            }
            catch (Exception ex)
            {

                return DBService.ReError<ReportInvStock>(ex.Message);
            }
        }
        #endregion

        #region 获得货位现存量
        public static RetModel<ReportInvStock> Rep_GetInvPosStock(string corg, string cwhcode, string cposcode, string cinvcode)
        {
            try
            {

                string condition = "";
                if (!string.IsNullOrEmpty(cwhcode) && cwhcode != "")
                {
                    condition += String.Format(@" and a.cWhCode='{0}'", cwhcode);
                }
                if (!string.IsNullOrEmpty(cinvcode) && cinvcode != "")
                {
                    condition += String.Format(@" and A.cInvCode='{0}'", cinvcode);
                }
                if (!string.IsNullOrEmpty(cposcode) && cposcode != "")
                {
                    condition += String.Format(@" and (A.cPosCode='{0}' or  p.cPosName='{0}') ", cposcode);
                }
                string sql = String.Format(@" SELECT a.cWhCode,b.cWhName,a.cPosCode,p.cPosName,  a.cInvCode,i.cInvName,convert(varchar(20),convert(real,SUM(a.iQuantity)))  AS iQuantity 
 FROM dbo.InvPositionSum a
 INNER JOIN warehouse b  ON  a.cWhCode=b.cwhcode
 INNER JOIN dbo.Position p ON a.cPosCode = p.cPosCode
 INNER JOIN  dbo.Inventory i on a.cInvCode=i.cInvCode
where 1= 1 {0}  
 GROUP BY  a.cWhCode,b.cWhName,a.cPosCode,p.cPosName,a.cInvCode,i.cInvName  
order by iQuantity desc
", condition);


                List<ReportInvStock> lst = SqlsugarHelper._Instance.Ado.SqlQuery<ReportInvStock>(sql).ToList();
                if (lst.Count > 0)
                {
                    return DBService.ReOK<ReportInvStock>(lst);
                }
                else
                {
                    return DBService.ReError<ReportInvStock>("没有现存量");
                }
            }
            catch (Exception ex)
            {

                return DBService.ReError<ReportInvStock>(ex.Message);
            }
        }
        #endregion

        
        //不是list的，直接是类
        //public static RetModel2 ReOKObject(object reData)
        //{
        //    RetModel2 retModel = new RetModel2();
        //    retModel.code = 200;
        //    retModel.message = "操作成功";
        //    retModel.data = reData;
        //    return retModel;
        //}

        //public static RetModel2 ReErrObject(string msg)
        //{
        //    RetModel2 retModel = new RetModel2();
        //    retModel.code = 300;
        //    retModel.message = msg;
        //    retModel.data = null;
        //    return retModel;
        //}

        //public static RetModel ReOK(List<object> reData)
        //{
        //    RetModel retModel = new RetModel();
        //    retModel.code = 200;
        //    retModel.message = null;
        //    retModel.data = reData;
        //    return retModel;
        //}
        //public static RetModel ReError(string msg)
        //{
        //    RetModel retModel = new RetModel();
        //    retModel.code = 300;
        //    retModel.message = msg;
        //    retModel.data = null;
        //    return retModel;
        //}
        //public static RetModel<T> ReOK<T>(List<T> reData)
        //{
        //    RetModel<T> retModel = new RetModel<T>();
        //    retModel.code = 200;
        //    retModel.message = null;
        //    retModel.data = reData;
        //    return retModel;
        //}
        //public static RetModel<T> ReError<T>(string msg)
        //{
        //    RetModel<T> retModel = new RetModel<T>();
        //    retModel.code = 300;
        //    retModel.message = msg;
        //    return retModel;
        //}


        //public static RetModelbool ReOKbool()
        //{
        //    RetModelbool retModel = new RetModelbool();
        //    retModel.code = 200;
        //    //retModel.data = true; 
        //    return retModel;
        //}

        //public static RetModelbool ReOKbool(string msg)
        //{
        //    RetModelbool retModel = new RetModelbool();
        //    retModel.code = 200;
        //    retModel.message = msg;
        //    //retModel.data = true;
        //    return retModel;
        //}
        //public static RetModelbool ReErrorbool(string msg)
        //{
        //    RetModelbool retModel = new RetModelbool();
        //    retModel.code = 300;
        //    retModel.message = msg;
        //    //retModel.data = false;
        //    return retModel;
        //}



    }
}
